package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.WorkQuality;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;


/**
 * 作业质量dao
 * @author Administrator
 *
 */
public class WorkQualityDaoImp implements CommnonDao<WorkQuality>{

	@Override
	public long save(WorkQuality value) throws Exception {
		String sql="insert into t_work_quality values(null,?,?,?,?);";
		long num=JdbcUtil.insertAuto(sql,
				value.getWorkSubmitId(),
				value.getWorkLevel(),
				value.getCreateTime(),
				value.getWorkState());
		return num;
	}

	@Override
	public int update(WorkQuality value) throws Exception {
		String sql="update t_work_quality set work_submit_id=?,work_level=?,create_time=?,"
				+ "work_state=? where work_quality_id=?;";
		int num=JdbcUtil.update(sql,
				value.getWorkSubmitId(),
				value.getWorkLevel(),
				value.getCreateTime(),
				value.getWorkState(),
				value.getWorkQualityId());
		return num;
	}

	@Override
	public int delete(WorkQuality value) throws Exception {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public List<WorkQuality> findAll(int pageNo, int pageSize) throws Exception {
		return null;
	}
	/**
	 * 根据学生编号查询已提交作业
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<WorkQuality> findAll(Object key, int pageNo, int pageSize)
			throws Exception {
		String sql="SELECT t.tch_name,h.course_name,h.create_time,q.work_state,q.work_level "
				+ " FROM t_work_quality AS q "
				+ " LEFT JOIN t_work_submit AS s ON s.work_submit_id=q.work_submit_id"
				+ " LEFT JOIN t_homework AS h ON s.homeword_id=h.homework_id "
				+ " LEFT JOIN t_teacher AS t ON h.tch_id=t.tch_id"
				+ " WHERE s.stu_id=?"
				+ " LIMIT ?,?;";
		List<WorkQuality> works=JdbcUtil.query(WorkQuality.class, sql,key, (pageNo-1)*pageSize,pageSize);
		return works;
	}
	/**
	 * 根据学生编号和老师姓名或科目查询已提交作业
	 * @param stuId
	 * @param key
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<WorkQuality> findAll(Integer stuId,Object key, int pageNo, int pageSize)
			throws Exception{
		String sql="SELECT * FROM t_work_quality"
				+ " LEFT JOIN t_work_submit ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " LEFT JOIN t_homework ON t_work_submit.homeword_id=t_homework.homework_id"
				+ " LEFT JOIN t_teacher ON t_homework.tch_id=t_teacher.tch_id"
				+ " WHERE t_work_submit.stu_id=? AND t_teacher.tch_name=? OR t_homework.course_name=?"
				+ " LIMIT ?,?;";
		List<WorkQuality> works=JdbcUtil.query(WorkQuality.class, sql,stuId,key,key, (pageNo-1)*pageSize,pageSize);
		return works;
	}
	
	@Override
	public WorkQuality findByKey(Object key) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Map<String, Object>> getMapList() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	/**
	 * 根据学生编号查询已提交作业数据总页数
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public long selectPage(int pageSize,Object arg) throws Exception{
		String sql="SELECT count(*) FROM t_work_quality"
				+ " LEFT JOIN t_work_submit ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " LEFT JOIN t_homework ON t_work_submit.homeword_id=t_homework.homework_id"
				+ " LEFT JOIN t_teacher ON t_homework.tch_id=t_teacher.tch_id"
				+ " WHERE t_work_submit.stu_id=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,arg);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	/**
	 * 根据学生编号查询已提交作业数据总页数
	 * @param pageSize
	 * @return
	 * @throws SQLException
	 */
	public long selectPage(int pageSize,Object arg,Object content) throws Exception{
		String sql="SELECT count(*) FROM t_work_quality"
				+ " LEFT JOIN t_work_submit ON t_work_submit.work_submit_id=t_work_quality.work_submit_id"
				+ " LEFT JOIN t_homework ON t_work_submit.homeword_id=t_homework.homework_id"
				+ " LEFT JOIN t_teacher ON t_homework.tch_id=t_teacher.tch_id"
				+ " WHERE t_work_submit.stu_id=? AND t_teacher.tch_name=? OR t_homework.course_name=?";
		long page=(long) JdbcUtil.uniqueValue(sql,1,arg,content,content);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	/**
	 * 计算作业合格率
	 * @param className
	 * @param countScore
	 * @return
	 * @throws Exception
	 */
	public double qualified(String className,double countScore) throws Exception{
		String sql="SELECT COUNT(*) FROM t_student WHERE stu_class_name=?;";
		long count=(long) JdbcUtil.uniqueValue(sql, 1, className);
		double c=(double)count;
		double a=countScore/c;
		return a;
	}
	/**
	 * 计算作业提交率
	 * @param className
	 * @param countWork
	 * @return
	 * @throws Exception
	 */
	public double submission(String className,double countWork) throws Exception{
		String sql="SELECT COUNT(*) FROM t_student WHERE stu_class_name=?;";
		long count=(long) JdbcUtil.uniqueValue(sql, 1, className);
		double c=(double)count;
		double a=countWork/c;
		return a;
	}

}
